package com.hmkj.core.mapper.user;

import com.hmkj.common.baseMapper.GenericMapper;
import com.hmkj.core.model.user.UserModel;
import com.hmkj.core.po.user.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * 用户表相关Dao接口<br>
 *
 * @author donfy
 * @date 2017-08-25 04:51:01
 */
public interface UserMapper extends GenericMapper<User, Integer> {

    /**
     * 用户列表
     *
     * @param params
     * @return
     */
    @Select("SELECT t.* ,v.user_name AS 'v.userName' FROM biz_user t LEFT JOIN biz_user v ON t.invite_id =v.id WHERE 1 = 1 ${whereSql} ${sortSql} limit ${nowPage}, ${pageSize}")
    List<UserModel> findListBySqlJoin(Map<String, Object> params);

    /**
     * 用户统计
     * @param params
     * @return
     */
    @Select("SELECT count(1) FROM biz_user t LEFT JOIN biz_user v ON t.invite_id =v.id WHERE 1 = 1 ${whereSql}")
    long countBySqlJoin(Map<String, Object> params);

    /**
     * 添加代理商
     * @param userIds1
     * @param types
     * @return
     */
    @Select("SELECT t.* FROM biz_user t where t.id in (#{userIds1}) AND t.user_type in (#{types}) AND t.del_flag = 1")
    List<User> findAgentBySql(@Param("userIds1") String userIds1,@Param("types") String types);

    /**
     * 获取所有下级中是客户角色的用户
     * @param userIds1
     * @param types
     * @return
     */
    @Select("SELECT t.* FROM biz_user t WHERE t.id in (#{userIds}) AND u.user_type in (#{types}) AND u.del_flag = 1")
    List<User> findUserRole(@Param("userIds") String userIds1,@Param("types") String types);

    /**
     * 好友列表
     *
     * @param params
     * @return
     */
    @Select("SELECT t.*, IFNULL(parentUser.real_name,'') AS 'parentUser.realName', IFNULL(parentuser.mobile,'') AS 'parentuser.mobile' FROM biz_user t LEFT JOIN biz_user parentUser ON t.parent_id = parentUser.id WHERE 1 = 1 ${whereSql} ${sortSql} limit ${nowPage}, ${pageSize} ")
    List<UserModel> findListForFriendsBySqlJoin(Map<String, Object> params);

    /**
     * 好友统计
     *
     * @param params
     * @return
     */
    @Select("SELECT COUNT(1) FROM biz_user t LEFT JOIN biz_user parentUser ON t.parent_id = parentUser.id WHERE 1 = 1 ${whereSql} ${sortSql}")
    long countForFriendsBySqlJoin(Map<String, Object> params);

    /**
     * 统计顶级用户数量
     *
     * @return
     */
    @Select("SELECT COUNT(*) FROM biz_user t WHERE 1 = 1 AND del_flag = 1 AND LENGTH(CODE) - LENGTH(REPLACE(CODE, '.', '')) = 0")
    Integer countTopByLevel();

    /**
     * 统计第 N 级好友数量
     *
     * @param params 参数中必须包含以下参数：
     *               offset 偏移量（表示要统计的用户的节点编号中点的个数）
     *               level 要统计伙伴数的层级（level 为空，查询所用层级的伙伴总数）
     *               code 要统计的用户的节点编号
     * @return
     */
    @Select("<script>SELECT  count(*)  FROM  biz_user t  where 1=1 AND del_flag = 1  <if test=\"code != null and code != ''\">   AND code LIKE  CONCAT('${code}', '.%')  </if>  <if test=\"level != null and level != ''\">   AND length(code)-length(replace(code,'.','')) = (#{level} + #{offset})  </if>  <if test=\"whereSql != null and whereSql != ''\">   ${whereSql}  </if>  <if test=\"sortSql != null and sortSql != ''\">   ${sortSql}  </if></script>")
    Integer countPartnerByLevel(Map<String, Object> params);

    /**
     * 统计第 N 级好友
     *
     * @param params 参数中必须包含以下参数：
     *               offset 偏移量（表示要统计的用户的节点编号中点的个数）
     *               level 要统计伙伴数的层级（level 为空，查询所用层级的伙伴总数）
     *               code 要统计的用户的节点编号
     * @return
     */
    @Select("<script>SELECT  t.* FROM  biz_user t  where 1=1 AND del_flag = 1  <if test=\"code != null and code != ''\">   AND code LIKE  CONCAT('${code}', '.%')  </if>  <if test=\"level != null and level != ''\">   AND length(code)-length(replace(code,'.','')) = (#{level} + #{offset})  </if>  <if test=\"whereSql != null and whereSql != ''\">   ${whereSql}  </if>  <if test=\"sortSql != null and sortSql != ''\">   ${sortSql}  </if></script>")
    List<User> listPartnerByLevel(Map<String, Object> params);

    /**
     * 累计投资人数
     *
     * @param params
     * @return
     */
    @Select("SELECT COUNT(1) FROM biz_user t WHERE  1=1 ${whereSql}")
    long investorSum(Map<String, Object> params);

    /**
     * 前台好友列表
     *
     * @param param
     * @return
     */
    @Select("SELECT t.*,a.tender_count AS tenderCount FROM biz_user t LEFT JOIN biz_account a ON a.user_id = t.id WHERE t.invite_id = ${userId} AND t.del_flag = 1 ORDER BY t.create_time DESC ")
    List<UserModel> findListForFriendsList(Map<String, Object> param);

    /**
     * 前台好友列表
     *
     * @param param
     * @return
     */
    @Select("SELECT count(*) FROM biz_user t LEFT JOIN biz_account a ON a.user_id = t.id  WHERE t.invite_id = ${userId} AND t.del_flag = 1 ORDER BY t.create_time DESC ")
    Integer findCountForFriendsList(Map<String, Object> param);

    /**
     * 根据手机号或邀请码查询用户
     *
     * @param inviteCode
     * @return
     */
    @Select("SELECT * FROM biz_user WHERE 1=1 and invite_code = #{inviteCode} and del_flag = 1 limit 1")
    User selectByInviteCode(@Param(value = "inviteCode") String inviteCode);

    /**
     * 根据手机号查询用户
     *
     * @param mobile
     * @return
     */
    @Select("SELECT * FROM biz_user WHERE 1=1 and mobile_phone = #{mobile} and del_flag = 1 limit 1")
    User selectByMobile(@Param(value = "mobile") String mobile);



    @Select("SELECT t.* FROM biz_user t  WHERE 1 = 1 ${whereSql}  limit ${nowPage}, ${pageSize} ")
    List<User> findListBySql(Map<String, Object> params);


    /**
     * 后台统计用户来源
     *
     * @param params
     * @return
     */
    @Select("SELECT COUNT(1) FROM (SELECT COUNT(1) FROM biz_user t WHERE 1 = 1 ${whereSql} ${sortSql}) t1")
    long countModelBySqlJoin(Map<String, Object> params);

    /**
     * 后台统计用户来源
     *
     * @param params
     * @return
     */
    @Select(" SELECT SUM(1) AS userNum, date_format(t.create_time, '%Y-%m-%d') AS registerTime FROM biz_user t" +
            " WHERE 1 = 1 ${whereSql}  ${sortSql} limit ${nowPage}, ${pageSize}")
    List<UserModel> findModelListBySqlJoin(Map<String, Object> params);

    @Select("SELECT t.source FROM biz_user t WHERE t.type = ${type} AND t.del_flag = ${delFlag} GROUP BY t.source")
    List<String> getSource(Map<String, Object> params);

    /**
     * 更新用户积分
     *
     * @param point 积分变化量
     * @param id    ID
     */

    @Update("UPDATE biz_user SET point = point + (#{point}) WHERE  id = #{id} AND point + (#{point}) >= 0")
    int updatePoint(@Param(value = "point")int point, @Param(value = "id")Integer id);


    /**
     * 用户下级
     */
    @Select("SELECT t.id, t.user_name, t.create_time FROM biz_user t WHERE t.parent_id = #{parentId} AND t.del_flag = 1 ORDER BY t.create_time DESC")
    List<User> selectOffline(@Param(value = "parentId")Integer parentId);

    /**
     * 获取已有持仓用户
     */
    @Select("SELECT t.* FROM biz_user t WHERE t.del_flag = 1 AND t.id IN (SELECT user_id FROM biz_order WHERE del_flag = 1 AND commo_id = ${commoId} AND status = 3 GROUP BY user_id)")
    List<User> selectHoldingOrderByUser(@Param(value = "commoId")Integer commoId);


    /**
     * 查询最后一条记录
     * @return
     */
    @Select("SELECT t.* FROM biz_user t WHERE t.del_flag = 1 ORDER BY t.id DESC LIMIT 1")
    User selectLastRecord();

}
